﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Dapper;
using BCEntity.TMS.Driver.DriverExamine;
using BCCommon;
using System.Threading.Tasks;

namespace BCData.TMS.Driver.DriverExamine
{
    public class DriverExamineTestQuestionData : IDriverExamineTestQuestionData
    {
        private const string QUERY_SQL = "SELECT A.TestQuestionNo,A.PaperId,A.DatumId,A.DatumAnswerId,A.CreateTime,B.DatumTopic,B.CreateTime as LearndDatumCreateTime,B.ModifyAdminId,B.ModifyDateTime,C.CorrectAmount,C.ErrorAmount,C.CreateTime as PaperCreateTime,C.EndTime   " +
                                        "FROM DriverExamineTestquestion A " +
                                        "LEFT JOIN DriverLearnDatum B ON  A.DatumId  = B.DatumId " +
                                        "LEFT JOIN DriverExaminePapers C ON A.PaperId=C.PaperId ";

        private readonly IDatabaseContext databaseContext;

        public DriverExamineTestQuestionData(IDatabaseContext databaseContext)
        {
            this.databaseContext = databaseContext;
        }

        /// <summary>
        /// 新增司机考试试题
        /// </summary>
        /// <param name="entity">司机考试试题</param>
        /// <returns></returns>
        public async Task<DriverExamineTestQuestionEntity> InsertAsync(DriverExamineTestQuestionEntity entity)
        {
            string sql = @"INSERT INTO DriverExamineTestquestion(TestQuestionNo,PaperId,DatumId,DatumAnswerId,CreateTime)
                           VALUES(?TestQuestionNo,?PaperId,?DatumId,?DatumAnswerId,?CreateTime);
                           SELECT  TestQuestionNo,PaperId,DatumId,DatumAnswerId,CreateTime FROM DriverExamineTestquestion WHERE TestQuestionNo=?TestQuestionNo AND PaperId=?PaperId; ";
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?TestQuestionNo", entity.TestQuestionNo, DbType.AnsiString, size: 36);
            parameter.Add("?PaperId", entity.PaperId, DbType.Int64);
            parameter.Add("?DatumId", entity.DatumId, DbType.Int32);
            parameter.Add("?DatumAnswerId", entity.DatumAnswerId, DbType.Int32);
            parameter.Add("?CreateTime", DateTime.Now, DbType.DateTime);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<DriverExamineTestQuestionEntity>(sql, parameter);
        }


        /// <summary>
        /// 获取考试试题正确答案数
        /// </summary>
        /// <param name="datumIds">学习资料Id列表</param>
        /// <returns></returns>
        public async Task<int> GetDatumCorrectAnswers(int[] datumIds)
        {
            string ids = string.Empty;
            if (datumIds != null && datumIds.Any())
            {
                ids = string.Join(",", datumIds);
            }
            string sql = string.Format(@"SELECT count(1) from DriverExamineTestquestionAnswer WHERE DatumAnswerId IN (SELECT DatumAnswerId from DriverLearnDatum WHERE DatumId in({0}))", string.Join(",", ids));
            var result = await this.databaseContext.Master.ExecuteScalarAsync<int>(sql);
            return result;
        }

        /// <summary>
        /// 获取考试试题错误答案数
        /// </summary>
        /// <param name="datumIds">学习资料Id列表</param>
        /// <returns></returns>
        public async Task<int> GetDatumErrorAnswers(int[] datumIds)
        {
            string ids = string.Empty;
            if (datumIds != null && datumIds.Any())
            {
                ids = string.Join(",", datumIds);
            }
            string sql = string.Format(@"SELECT count(1) from DriverExamineTestquestionAnswer WHERE DatumAnswerId NOT IN (SELECT DatumAnswerId from DriverLearnDatum WHERE DatumId in({0}))", string.Join(",", ids));
            var result = await this.databaseContext.Master.ExecuteScalarAsync<int>(sql);
            return result;
        }

        /// <summary>
        /// 根据试卷Id获取司机考试试题
        /// </summary>
        /// <param name="paperId">试卷Id</param>
        /// <returns></returns>
        public async Task<IEnumerable<DriverExamineTestQuestionEntity>> GetDriverExamineTestQuestionsByPaperId(long? paperId)
        {
            DynamicParameters parameters = new DynamicParameters();
            List<string> wheres = new List<string>();
            if (paperId.HasValue)
            {
                wheres.Add("A.PaperId=?PaperId");
                parameters.Add("?PaperId", paperId.Value, DbType.Int64);
            }

            string sql = string.Format("{0} {1};",
                              QUERY_SQL,
                              wheres.Count > 0 ? "WHERE " + string.Join(" AND ", wheres) : string.Empty);

            return await this.databaseContext.Slave.QueryAsync<DriverExamineTestQuestionEntity>(sql, parameters);
        }

        /// <summary>
        /// 根据试题编号和试卷Id检查是否存在
        /// </summary>
        /// <param name="TestQuestionNo">试题编号</param>
        /// <param name="paperId">试卷Id</param>
        /// <returns></returns>
        public async Task<bool> CheckExistsByQuestionsNoAndPaperId(string TestQuestionNo, long paperId)
        {
            string sql = @"SELECT 1 FROM DriverExamineTestquestion  WHERE TestQuestionNo =?TestQuestionNo AND PaperId=?PaperId LIMIT 1";
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?TestQuestionNo", TestQuestionNo, DbType.AnsiString, size: 36);
            parameter.Add("?PaperId", paperId, DbType.Int64);
            var result = await this.databaseContext.Master.ExecuteScalarAsync(sql, parameter);
            return result != null;
        }

        /// <summary>
        /// 根据学习资料获取考试试题
        /// </summary>
        /// <param name="datumId">学习资料Id</param>
        /// <returns></returns>
        public async Task<IEnumerable<DriverExamineTestQuestionEntity>> GetDriverExamineTestQuestionsByDatumId(long? datumId)
        {
            DynamicParameters parameters = new DynamicParameters();
            List<string> wheres = new List<string>();
            if (datumId.HasValue)
            {
                wheres.Add("A.DatumId=?DatumId");
                parameters.Add("?DatumId", datumId.Value, DbType.Int64);
            }
            string sql = string.Format("{0} {1};", QUERY_SQL, wheres.Count > 0 ? "WHERE " + string.Join(" AND ", wheres) : string.Empty);
            return await this.databaseContext.Master.QueryAsync<DriverExamineTestQuestionEntity>(sql, parameters);
        }


        /// <summary>
        /// 批量删除考试试题
        /// </summary>
        /// <param name="datumIds">学习资料Id</param>
        /// <returns></returns>

        public async Task<int> DeleteByDatumIds(int[] datumIds)
        {
            string ids = string.Empty;
            if (datumIds != null && datumIds.Any())
            {
                ids = string.Join(",", datumIds);
            }
            string sql = string.Format(@"DELETE from DriverExamineTestquestion WHERE  DatumId IN ({0})", string.Join(",", ids));
            return await this.databaseContext.Master.ExecuteScalarAsync<int>(sql);
        }
    }
}
